https://gibs.com RSS feeds for Global Internet Business Solutions ~ GIBS 60 https://gibs.com/Support/Knowledge-Base/ID/331/nopCommerce_SQL_Query_for_Top_10_Gross_Sales_Days#Comments 0 https://gibs.com/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=51&ModuleID=411&ArticleID=331 https://gibs.com:443/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=331&PortalID=0&TabID=51 https://gibs.com/Support/Knowledge-Base/ID/331/nopCommerce_SQL_Query_for_Top_10_Gross_Sales_Days Declare @current_utc_offset int IF  ServerProperty('EngineEdition') >= 5 Begin set @current_utc_offset = (select  convert(int, left(current_utc_offset,3)) AS current_utc_offset from sys.time_zone_info Where Name = 'US Eastern Standard Time') End PRINT @current_utc_offset SELECT TOP 10 SUM([OrderTotal]) as totalSales, Count([Id]) AS Orders,  dateadd(hour, @current_utc_offset, dateadd(hour, datediff(hour, 0, convert(date, [CreatedOnUtc])), 0)) FROM [dbo].[Order] GROUP BY dateadd(hour, @current_utc_offset, dateadd(hour, datediff(hour, 0, convert(date, [CreatedOnUtc])), 0)) ORDER BY SUM([OrderTotal]) desc Webmaster Tue, 04 Feb 2025 15:26:00 GMT f1397696-738c-4295-afcd-943feb885714:331 https://gibs.com/Support/Knowledge-Base/ID/332/nopCommerce_SQL_Query_for_Top_10_Sales_Days#Comments 0 https://gibs.com/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=51&ModuleID=411&ArticleID=332 https://gibs.com:443/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=332&PortalID=0&TabID=51 https://gibs.com/Support/Knowledge-Base/ID/332/nopCommerce_SQL_Query_for_Top_10_Sales_Days Declare @current_utc_offset int IF  ServerProperty('EngineEdition') >= 5 Begin set @current_utc_offset = (select  convert(int, left(current_utc_offset,3)) AS current_utc_offset from sys.time_zone_info Where Name = 'US Eastern Standard Time') End PRINT @current_utc_offset SELECT TOP 10 SUM([OrderTotal]) as totalSales, Count([Id]) AS Orders,  dateadd(hour, @current_utc_offset, dateadd(hour, datediff(hour, 0, convert(date, [CreatedOnUtc])), 0)) FROM [dbo].[Order] GROUP BY dateadd(hour, @current_utc_offset, dateadd(hour, datediff(hour, 0, convert(date, [CreatedOnUtc])), 0)) ORDER BY SUM([OrderTotal]) desc Webmaster Tue, 04 Feb 2025 15:26:00 GMT f1397696-738c-4295-afcd-943feb885714:332 https://gibs.com/Support/Knowledge-Base/ID/328/nopCommerce_-_Deleting_Guest_Accounts#Comments 0 https://gibs.com/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=51&ModuleID=411&ArticleID=328 https://gibs.com:443/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=328&PortalID=0&TabID=51 https://gibs.com/Support/Knowledge-Base/ID/328/nopCommerce_-_Deleting_Guest_Accounts Deleting guest account from Customer table: SQL Query DELETE Customer FROM Customer LEFT OUTER JOIN [Order] ON Customer.Id = [Order].CustomerId WHERE ([Order].Id IS NULL) AND (Customer.Email IS NULL) delete guest account - nopCommerce Webmaster Sun, 12 Jan 2025 10:01:00 GMT f1397696-738c-4295-afcd-943feb885714:328 https://gibs.com/Support/Knowledge-Base/ID/4/Azure_SQL_uses_Greenwich_Mean_Time#Comments 0 https://gibs.com/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=51&ModuleID=411&ArticleID=4 https://gibs.com:443/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=4&PortalID=0&TabID=51 https://gibs.com/Support/Knowledge-Base/ID/4/Azure_SQL_uses_Greenwich_Mean_Time // AZURE SQL USES GREENWICH MEAN TIME . . . Azure SQL Database does not support time zone settings; it always follows UTC. Use AT TIME ZONE in SQL Database if you need to interpret date and time information in a non-UTC time zone. Declare @current_utc_offset int IF  ServerProperty('EngineEdition') >= 5 Begin set @current_utc_offset = (select  convert(int, left(current_utc_offset,3)) AS current_utc_offset from sys.time_zone_info Where Name = 'US Eastern Standard Time') End   Then I use this information to adjust the query data   SELECT  dateadd(hour, @current_utc_offset, dateadd(hour, datediff(hour, 0, [CreatedOnDate]), 0)) as TimeStampHour   Complete Query Used for report:   IF  ServerProperty('EngineEdition') >= 5 Begin set @current_utc_offset = (select  convert(int, left(current_utc_offset,3)) AS current_utc_offset from sys.time_zone_info Where Name = 'US Eastern Standard Time') End     if(@ServiceLocation='0' or @ServiceLocation is null) begin        set @ServiceLocation=null; end   SELECT  dateadd(hour, @current_utc_offset, dateadd(hour, datediff(hour, 0, [CreatedOnDate]), 0)) as TimeStampHour , Count(*) AS ClientsServed FROM  [GIBS_FBClientsVisits] where   ( [CreatedOnDate]     BETWEEN @StartDate      and @EndDate)     and  (@ServiceLocation is null or GIBS_FBClientsVisits.ServiceLocation=@ServiceLocation) GROUP BY dateadd(hour, datediff(hour, 0, [CreatedOnDate]), 0) ORDER BY dateadd(hour, datediff(hour, 0, [CreatedOnDate]), 0)   Thu, 23 May 2024 17:59:00 GMT f1397696-738c-4295-afcd-943feb885714:4 https://gibs.com/Support/Knowledge-Base/ID/13/Getting_Eastern_Standard_Time_from_Azure_SQL#Comments 0 https://gibs.com/DesktopModules/DnnForge%20-%20NewsArticles/RssComments.aspx?TabID=51&ModuleID=411&ArticleID=13 https://gibs.com:443/DesktopModules/DnnForge%20-%20NewsArticles/Tracking/Trackback.aspx?ArticleID=13&PortalID=0&TabID=51 https://gibs.com/Support/Knowledge-Base/ID/13/Getting_Eastern_Standard_Time_from_Azure_SQL Declare @todays_date Date set @todays_date = CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS date) For example, if you want the current date and time as a datetime value like GETDATE() does, do this: CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS datetime) If you want the current date and time as a datetime2 value like SYSDATETIME() does, do this: CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS datetime2) And if you just want the current date, you can do this: CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS date) Tue, 03 Nov 2020 16:20:00 GMT f1397696-738c-4295-afcd-943feb885714:13